﻿using Model;
using System;
using System.Data;
using System.Text;
using Utility.Helper;

namespace BLL
{
    public class Qs_Base_SetBLL : BaseBLL
    {
        /// <summary>
        /// 查询自己的
        /// </summary>
        /// <param name="comId"></param>
        /// <returns></returns>
        public DataTable SelectModel()
        {
            //SQL 语句
            string sql = string.Format("select b.set_id,b.set_name,b.set_value from qs_base_set b order by b.set_id");
            return base.Query(sql, "", "", "", "");
        }

        /// <summary>
        /// 查询自己的
        /// </summary>
        /// <param name="comId"></param>
        /// <returns></returns>
        public DataTable SelectModel(string str)
        {
            //SQL 语句
            string sql = string.Format(@"select b.set_id, b.set_name, b.set_value
	from qs_base_set b
 where (b.set_name like '%" + str + "%' or b.set_value like '%" + str + "%') order by b.set_id");
            return base.Query(sql, "", "", "", "");
        }

        public int InsertModel(Qs_Base_Set model, string operName)
        {
            model.Set_id = Convert.ToInt32(base.GetSequence("SET_ID"));
            string sql = @"INSERT INTO Qs_Base_Set(
	                            SET_NAME,
	                            SET_VALUE,
	                            SET_ID,
	                            REMARK
                            )
                            VALUES
                            (
	                            :SET_NAME,
	                            :SET_VALUE,
	                            :SET_ID,
	                            :REMARK
                            )";
            var parms = ParmsHelper.GetParms();
            parms.Add(":SET_NAME", model.Set_name);
            parms.Add(":SET_VALUE", model.Set_value);
            parms.Add(":REMARK", model.REMARK);
            parms.Add(":SET_ID", model.Set_id);
            return base.NoQuery(sql, parms);
        }

        public int Delete(string ids, string operName)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 1;
            }
            var sql = string.Format("DELETE QS_BASE_SET WHERE SET_ID IN ({0})", ids);
            return base.NoQuery(sql, operName);
        }

        public int UpdateModel(Qs_Base_Set model, string operName)
        {
            var sql = @"UPDATE Qs_Base_Set T1
                        SET
                            T1.SET_NAME = :SET_NAME,
                            T1.SET_VALUE = :SET_VALUE,
                            T1.REMARK = :REMARK
                        WHERE
                            T1.SET_ID = :SET_ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":SET_NAME", model.Set_name);
            parms.Add(":SET_VALUE", model.Set_value);
            parms.Add(":REMARK", model.REMARK);
            parms.Add(":SET_ID", model.Set_id);
            return base.NoQuery(sql, parms);
        }

        public Qs_Base_Set GetModelById(string SetId)
        {
            string sql = string.Format("SELECT b.Set_id,b.Set_name,b.Set_value,b.REMARK FROM Qs_Base_Set b WHERE b.Set_id=" + SetId);
            DataTable dt = base.Query(sql, "", "", "", "");
            Qs_Base_Set SET = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    SET = new Qs_Base_Set();
                    SET.Set_id = int.Parse(item[0].ToString());
                    SET.Set_name = item[1].ToString();
                    SET.Set_value = item[2].ToString();
                    SET.REMARK = item[3].ToString();
                }
            }
            return SET;
        }

        public Qs_Base_Set GetModelByName(string SetName)
        {
            string sql = "SELECT b.Set_id,b.Set_name,b.Set_value FROM Qs_Base_Set b WHERE b.Set_name=:SET_NAME";
            var parms = ParmsHelper.GetParms();
            parms.Add(":SET_NAME", SetName);
            DataTable dt = base.Query(sql, parms);
            Qs_Base_Set SET = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    SET = new Qs_Base_Set();
                    SET.Set_id = int.Parse(item[0].ToString());
                    SET.Set_name = item[1].ToString();
                    SET.Set_value = item[2].ToString();
                }
            }
            return SET;
        }

        public int GetRecordCount(string whereStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1) FROM THKQS.Qs_Base_Set b WHERE 1=1");
            if (!string.IsNullOrEmpty(whereStr))
            {
                strSql.Append(whereStr);
            }
            return Convert.ToInt32(base.Query(strSql.ToString(), "", "", "", "").Rows[0][0]);
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY b." + orderby + @") as r,b.Set_id,b.Set_name,b.Set_value,b.REMARK FROM THKQS.Qs_Base_Set b WHERE 1=1 ");
            if (!string.IsNullOrEmpty(whereStr.Trim()))
            {
                strSql.Append(whereStr);
            }
            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return base.Query(strSql.ToString(), "", "", "", "");
        }
    }
}